﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Reflection;
public class MysqlManager
{
    string ip = "127.0.0.1";
    string username = "root";
    string password = "123456";
    string dbName = "qikuedu";

    MySqlConnection connect;
    MySqlCommand command;
    public MysqlManager()
    {
        connect = new MySqlConnection();
        connect.ConnectionString = "Server=" + ip + ";user id=" + username + ";password=" + password + ";database=" + dbName;
        connect.Open();
        Console.WriteLine("打开数据库成功");

        command = connect.CreateCommand();

        //Dictionary<string, string> skills = new Dictionary<string, string>();
        //skills.Add("id","int primary key");
        //skills.Add("name","varchar(10)");
        //skills.Add("attack","int");
        //skills.Add("ranges","float");

        //CreateTable("Skill",skills);//创建表
        //InsertInto("skill");//增
        //DeleteFrom("skill");//删
        //UpdateSet("skill");//改
        //SelcetFrom("skill");//查

        //User user = new User();
        //user.id = 1;
        //user.username = "123";
        //user.nickname = "234";
        //user.password = "345";

        //CreateTable(user);

        List<User> users = SelcetFrom<User>();
    }
    void Close()
    {
        command.Dispose();
        connect.Dispose();
    }

    void CreateTable<T>(T t) where T:class
    {
        Type type = typeof(T);
        string tableName = type.Name;
        FieldInfo[] fileds = type.GetFields();

        string sql = "create table " + tableName + " (";
        foreach (var item in fileds)
        {
            if (item.Name=="id")
                sql += item.Name + " " + CS2DB(item.FieldType) + " primary key auto_increment,";
            else
                sql += item.Name + " " + CS2DB(item.FieldType) + ",";
        }
        sql = sql.TrimEnd(',') + ");";
        ExcuteSql(sql);
    }
    string CS2DB(Type type)
    {
        string dbType = "";
        if (type.Name == "String")
        {
            dbType = "Text";
        }
        else if (type.Name == "Int32")
        {
            dbType = "Int";
        }
        else if (type.Name == "Float")
        {
            dbType = "Float";
        }
        return dbType;
    }
    //创建表
   void CreateTable(string tableName, Dictionary<string, string> infos)
    {
        string sql = "create table " + tableName + "(";
        foreach (var item in infos)
        {
            sql += item.Key + " " + item.Value + ",";
        }
        sql = sql.TrimEnd(',') + ");";

        ExcuteSql(sql);
    }

    //增
    void InsertInto(string tableName)
    {
        string sql = "insert into " + tableName + " " + "values(1,'暴风雪',10,10)";
        ExcuteSql(sql);
    }

    void InsertInto<T>(T t) where T:class
    {
        Type type = typeof(T);
        string tableName = type.Name;
        FieldInfo[] fileds = type.GetFields();

        string sql = "insert into " + tableName + " values(";
        foreach (var item in fileds)
        {
            string value = item.GetValue(t).ToString();
            sql += "'" + value + "',";
        }
        sql = sql.TrimEnd(',')+")";
        ExcuteSql(sql);
    }
    //删
    void DeleteFrom(string tableName)
    {
        string sql = "delete from " + tableName + " " + "where id=1";
        ExcuteSql(sql);
    }
    void DeleteFrom<T>(int id)
    {
        string tableName = typeof(T).Name;
        string sql = "delete from " + tableName + " " + "where id= "+id;
        ExcuteSql(sql);
    }

    //改
    void UpdateSet(string tableName)
    {
        string sql = "update " + tableName + " " + "set name ='神罗天征' where id=1";
        ExcuteSql(sql);
    }
    void UpdateSet<T>(T t)
    {
        Type type = typeof(T);
        string tableName = type.Name;

        FieldInfo[] fields = type.GetFields();
        int id = int.Parse(type.GetField("id").GetValue(t).ToString());

        string sql = "update " + tableName + " " + "set ";
        foreach (FieldInfo item in fields)
        {
            if (item.Name!="id")
            {
                sql += item.Name + " = " + item.GetValue(t) + ",";
            }
        }
        sql = sql.TrimEnd(',');
        sql += " where id="+id;
        int count = ExcuteSql(sql);
        if (count>0)
        {
            Console.WriteLine("修改成功："+count+"行");
        }
    }

    //查
    void SelcetFrom(string tableName)
    {
        string sql = "select * from "+tableName;
        command.CommandText = sql;
        MySqlDataReader reader= command.ExecuteReader();//以阅读器来查询
        while (reader.Read())
        {
            int count = reader.FieldCount;
            for (int i = 0; i < count; i++)
            {
                string key = reader.GetName(i);//列名
                object value = reader.GetValue(i);//列值

                Console.WriteLine(key+":"+value);
            }
            
        }
    }

    T SelectByUserName<T>(string username) where T: class,new()
    {
        Type type = typeof(T);
        string tableName = type.Name;
        string sql = "select * from " + tableName + " where username=" + username;
        command.CommandText = sql;
        MySqlDataReader reader = command.ExecuteReader();
        if (reader.Read())
        {
            T t = Activator.CreateInstance<T>();
            //T t = new T();
            int count = reader.FieldCount;
            for (int i = 0; i < count; i++)
            {
                string key = reader.GetName(i);
                object value = reader.GetValue(i);

                type.GetField(key).SetValue(t,value);
                Console.WriteLine(key+":"+value);
            }
            return t;
        }
        return null;
    }
    List<T> SelcetFrom<T>() where T : class,new()
    {
        Type type = typeof(T);
        string tableName = type.Name;
        string sql = "select * from " + tableName;
        command.CommandText = sql;
        MySqlDataReader reader = command.ExecuteReader();//以阅读器来查询
        List<T> array = new List<T>();
        while (reader.Read())
        {
            T t = new T();
            int count = reader.FieldCount;
            for (int i = 0; i < count; i++)
            {
                string key = reader.GetName(i);//列名
                object value = reader.GetValue(i);//列值

                type.GetField(key).SetValue(t,value);//把t中的key字段，填充数据value
                Console.WriteLine(key + ":" + value);
            }
            array.Add(t);
        }
        return array;
    }
    int ExcuteSql(string sql)
    {
        Console.WriteLine("sql->" + sql);
        command.CommandText = sql;
        int effectCount= command.ExecuteNonQuery();
        return effectCount;
    }
    
}
